package br.com.knowledge.persistence.jdbc.services;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.struts2.interceptor.SessionAware;

import br.com.knowledge.persistence.hibernate.entities.BcdColunas;
import br.com.knowledge.persistence.hibernate.entities.BcdTabelas;
import br.com.knowledge.persistence.hibernate.entities.CadProjeto;
import br.com.knowledge.persistence.jdbc.ConnectionFactory;
import br.com.knowledge.persistence.jdbc.entities.PrimaryKey;
import br.com.knowledge.persistence.jdbc.entities.StoredProcedure;
import br.com.knowledge.persistence.jdbc.entities.Trigger;
import br.com.knowledge.view.struts.objects.DadosSessionJDBC;

import com.opensymphony.xwork2.ActionSupport;


public class JdbcServices extends ActionSupport implements SessionAware{

	private String name;
	private String remarks;
	private String coltype;
	private double length;
	private String nulls;
	private Boolean bNulls;
	private Map<String, Object> session;
	private List<CadProjeto> listaProjetos;
	private CadProjeto conectarProjeto;

	public static boolean testarConexao(DadosSessionJDBC dadosSessionJBDC) throws Exception{
		return ConnectionFactory.testConnection(dadosSessionJBDC.getConfigsgdb(), dadosSessionJBDC.getDriversgdb(), 
												dadosSessionJBDC.getNmusersgdb(), dadosSessionJBDC.getPasswordJDBC() );
	}

	
	
	
	public List<BcdTabelas> getSysDbaTablesColumns( DadosSessionJDBC dadosSessionJDBC){
		List<BcdTabelas> listaTableSysDba = new ArrayList<BcdTabelas>();
		Connection con = null;

		try {
			con = ConnectionFactory.getConnection(dadosSessionJDBC);
			String sql = "select name, remarks from systables where name not like 'SYS%'";
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				BcdTabelas tab = new BcdTabelas();
				name = rs.getString("name");
				remarks = rs.getString("remarks");
				tab.setNometabela(name);
				tab.setDescricao(remarks);
				List <BcdColunas> listaColunasSysDba = new ArrayList<BcdColunas>();
				listaColunasSysDba = getSysDbaColunsTables(name,dadosSessionJDBC);			
				Set s = new HashSet();	
				for(BcdColunas colunas:listaColunasSysDba){
					s.add(colunas);
				}	
				//tab.setBcdColunases(s);
				//tab.setBcdColunases_1(s);
				listaTableSysDba.add(tab);				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return listaTableSysDba;
	}
	
	
	
	
	
	
	public List<BcdTabelas> getSysDbaTablesName( DadosSessionJDBC dadosSessionJDBC){
		List<BcdTabelas> listaTableSysDba = new ArrayList<BcdTabelas>();
		Connection con = null;

		try {
			con = ConnectionFactory.getConnection(dadosSessionJDBC);
			String sql = "select name, remarks from systables where name not like 'SYS%'";
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				BcdTabelas tab = new BcdTabelas();
				name = rs.getString("name");
				remarks = rs.getString("remarks");
				tab.setNometabela(name);
				tab.setDescricao(remarks);
				listaTableSysDba.add(tab);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return listaTableSysDba;
	}

	public List<BcdColunas> getSysDbaColunsTables( String TbName, DadosSessionJDBC dadosSessionJDBC) throws SQLException {
		List<BcdColunas> listaColumnTableSysDba = new ArrayList<BcdColunas>();
		Connection con = null;
		try {
			//dadosSessionJDBC = (DadosSessionJDBC) session.get("dadosjdbc");
			//con = ConnectionFactory.getConnection(dadosSessionJDBC);
			con = ConnectionFactory.getConnection(dadosSessionJDBC);
			String sql = "select name, coltype, length, nulls from syscolumns where tbname = '" + TbName + "'";
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				BcdColunas col = new BcdColunas();
				name = rs.getString("name");
				coltype = rs.getString("coltype");
				length = rs.getFloat("length");
				nulls = rs.getString("nulls");
				col.setNome(name);
				col.setTamanho(length);
				if ( nulls.equals("N")){					
					col.setIndnulo(true);
				}
				else{
					col.setIndnulo(false);
				}
				listaColumnTableSysDba.add(col);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(con!=null) con.close();
		}
		return listaColumnTableSysDba;
	}

	public List<PrimaryKey> getSysDbaPkTables( String TbName) throws SQLException {
		List<PrimaryKey> listaPkTableSysDba = new ArrayList<PrimaryKey>();
		Connection con = null;
		try {
			//dadosSessionJDBC = (DadosSessionJDBC) session.get("dadosjdbc");
			//con = ConnectionFactory.getConnection(dadosSessionJDBC);
			String sql = "select colname from syspkconstraints where name = '" + TbName + "'";
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				PrimaryKey pk = new PrimaryKey();
				pk.setName(rs.getString("name"));
				pk.setcolName(rs.getString("colname"));
				listaPkTableSysDba.add(pk);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(con!=null) con.close();
		}
		return listaPkTableSysDba;
	}

	public List<Trigger> getSysDbaTrigger() throws SQLException {
		List<Trigger> listaTriggerSysDba = new ArrayList<Trigger>();
		Connection con = null;
		try {
			//dadosSessionJDBC = (DadosSessionJDBC) session.get("dadosjdbc");
			//con = ConnectionFactory.getConnection(dadosSessionJDBC);
			String sql = "select name, actiontime, triggerevent, text from systriggers";
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				Trigger tg = new Trigger();
				tg.setName(rs.getString("name"));
				tg.setActiontime(rs.getString("actiontime"));
				tg.setTriggerevent(rs.getString("triggerevent"));
				tg.setText(rs.getString("text"));				
				listaTriggerSysDba.add(tg);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(con!=null) con.close();
		}
		return listaTriggerSysDba;
	}

	public List<StoredProcedure> getSysDbaStoredProcedure() throws SQLException {
		List<StoredProcedure> listaStoredProcedureSysDba = new ArrayList<StoredProcedure>();
		Connection con = null;
		try {
			//dadosSessionJDBC = (DadosSessionJDBC) session.get("dadosjdbc");
			//con = ConnectionFactory.getConnection(dadosSessionJDBC);
			String sql = "select name, text from syscommands";
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				StoredProcedure sp = new StoredProcedure();
				sp.setName(rs.getString("name"));
				sp.setText(rs.getString("text"));
				listaStoredProcedureSysDba.add(sp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(con!=null) con.close();
		}
		return listaStoredProcedureSysDba;
	}
	
	
	


	public void setColtype(String coltype) {
		this.coltype = coltype;
	}


	public String getColtype() {
		return coltype;
	}


	public void setLength(float length) {
		this.length = length;
	}


	public double getLength() {
		return length;
	}


	public void setNulls(String nulls) {
		this.nulls = nulls;
	}


	public String getNulls() {
		return nulls;
	}


	public void setbNulls(Boolean bNulls) {
		this.bNulls = bNulls;
	}


	public Boolean getbNulls() {
		return bNulls;
	}

	public void setSession(Map<String, Object> arg0) {
		// TODO Auto-generated method stub

	}

	public void setListaProjetos(List<CadProjeto> listaProjetos) {
		this.listaProjetos = listaProjetos;
	}

	public List<CadProjeto> getListaProjetos() {
		return listaProjetos;
	}

	public void setConectarProjeto(CadProjeto conectarProjeto) {
		this.conectarProjeto = conectarProjeto;
	}

	public CadProjeto getConectarProjeto() {
		return conectarProjeto;
	}

}
